﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

using System.Data.SqlClient;
using System.Globalization;

public partial class Pages_ItemEdit : DatabasePage
{
    DataRow databaseRow;
    CultureInfo ci = new CultureInfo("hr-HR");

    #region Properties

    private string TableName
    {
        get
        {
            return Request.QueryString["TableName"].ToString();
        }
    }

    private string IdColumn
    {
        get
        {
            return Request.QueryString["IdColumn"].ToString();
        }
    }

    private string IdValue
    {
        get
        {
            if (Request.QueryString["IdValue"] != null)
            {
                return Request.QueryString["IdValue"].ToString();
            }
            else
            {
                return null;
            }
        }
    }

    #endregion

    protected void Page_Load(object sender, EventArgs e)
    {
        //CheckPassword();

        try
        {
            SetupForm();


            if (!IsPostBack)
            {
                if (IdValue != null)
                {
                    SetValuesFromDatabase();
                }
                if (Request.UrlReferrer != null)
                {
                    //hypGoBack.NavigateUrl = Request.UrlReferrer.AbsoluteUri;
                }
                else
                {
                    //hypGoBack.Visible = false;
                }
            }

        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }

    void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            lblMessage.Text = String.Empty;
            Save();
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

    private void AddControl(Table t, DataColumn col)
    {
        TableRow row = new TableRow();
        TableCell labelCell = new TableCell();
        TableCell controlCell = new TableCell();

        Label lbl = new Label();
        lbl.Text = col.ColumnName + ": ";
        labelCell.Controls.Add(lbl);
        row.Cells.Add(labelCell);

        if (col.DataType == typeof(string))
        {
            TextBox txt = new TextBox();
            txt.ID = col.ColumnName;
            txt.Width = 800;
            if (col.AutoIncrement)
            {
                txt.Enabled = false;
            }
            //txt.MaxLength = col.MaxLength;
            controlCell.Controls.Add(txt);
        }
        else if (col.DataType == typeof(DateTime))
        {
            TextBox txt = new TextBox();
            txt.ID = col.ColumnName;
            controlCell.Controls.Add(txt);
        }
        else if (col.DataType == typeof(bool))
        {
            DropDownList ddl = new DropDownList();
            ddl.ID = col.ColumnName;
            ListItem li = new ListItem("", "");
            ddl.Items.Add(li);
            li = new ListItem("False", "False");
            ddl.Items.Add(li);
            li = new ListItem("True", "True");
            ddl.Items.Add(li);

            controlCell.Controls.Add(ddl);
        }
        else
        {
            TextBox txt = new TextBox();
            txt.ID = col.ColumnName;
            txt.Attributes.Add("Name", "txt" + col.ColumnName);
            if (col.AutoIncrement)
            {
                txt.Enabled = false;
            }
            //txt.MaxLength = col.MaxLength;


            if (MappingRow(TableName, col.ColumnName) != null)
            {
                //txt.Width = 0;
                txt.Attributes.Add("style", "display:none;");
                Panel pan = new Panel();
                DropDownList ddl = new DropDownList();
                //ddl.SelecteVa
                //ddl.ClientID = "bla";
                //string jF = string.Format("SetTxtFromDdl('{0}','{1}')", "txt" + col.ColumnName, ddl.ClientID);
                //string jF = string.Format("SetTxtFromDdl('{0}','{1}')", "ctl00_GrupaId", "ctl00_ddlGrupaId");
                string jF = string.Format("SetTxtFromDdl(this)");

                ddl.ID = "ddl" + col.ColumnName;
                ddl.Attributes.Add("Name", ddl.ID);
                ddl.Attributes.Add("onChange", jF);

                FillDll(ddl, MappingRow(TableName, col.ColumnName));

                pan.Controls.Add(txt);
                pan.Controls.Add(ddl);
                controlCell.Controls.Add(pan);
            }
            else
            {
                controlCell.Controls.Add(txt);
            }
        }
        row.Cells.Add(controlCell);

        t.Rows.Add(row);
    }

    private void Save()
    {
        lblMessage.Text = string.Empty;
        string currentColumnName = string.Empty;
        try
        {
            string selectStr = string.Format("SELECT TOP 1 * FROM {0} ", TableName);
            //DataTable dt = DataAccess.GetDataTableSchema(ConnectionString, selectStr);
            DataTable dt = DBData.SampleRow(Session["ConnectionString"], TableName);


            SqlConnection connection = new SqlConnection(Session["ConnectionString"].ToString());
            SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

            SqlCommandBuilder builder = new SqlCommandBuilder();
            builder.DataAdapter = adapter;
            builder.ConflictOption = ConflictOption.OverwriteChanges;

            string updateCommandSql = builder.GetUpdateCommand(true).CommandText;
            string insertCommandSql = builder.GetInsertCommand(true).CommandText;

            //INSERT or UPDATE
            SqlCommand cmd;
            if (IdValue == null)
            {
                cmd = new SqlCommand(insertCommandSql, connection);
            }
            else
            {
                cmd = new SqlCommand(updateCommandSql, connection);
            }

            SqlParameter param;


            foreach (DataColumn col in dt.Columns)
            {
                currentColumnName = col.ColumnName;
                TextBox txt;
                if (!(col.AutoIncrement && IdValue == null))
                {
                    param = new SqlParameter();
                    param.ParameterName = "@" + col.ColumnName;
                    if (col.ColumnName == Request.QueryString["IdColumn"].ToString())
                    {
                        param.ParameterName = param.ParameterName.Replace("@", "@Original_");

                    }
                    if (col.DataType == typeof(bool))
                    {
                        DropDownList ddl = (DropDownList)FindControlR(col.ColumnName);
                        if (ddl.SelectedValue == "")
                        {
                            param.Value = DBNull.Value;
                        }
                        else if (ddl.SelectedValue == "True")
                        {

                            param.Value = 1;
                        }
                        else if (ddl.SelectedValue == "False")
                        {

                            param.Value = 0;
                        }
                    }
                    else if (col.DataType == typeof(DateTime))
                    {
                        txt = (TextBox)FindControlR(col.ColumnName);
                        if (txt.Text == "")
                        {
                            param.Value = DBNull.Value;
                        }
                        else
                        {

                            //param.Value = DateTime.ParseExact(txt.Text, "dd.MM.yyyy", null);
                            param.Value = DateTime.Parse(txt.Text, ci);
                        }
                    }
                    else if (col.DataType == typeof(decimal))
                    {
                        txt = (TextBox)FindControlR(col.ColumnName);
                        if (txt.Text == "")
                        {
                            param.Value = DBNull.Value;
                        }
                        else
                        {
                            param.Value = Decimal.Parse(txt.Text, ci);
                        }
                    }
                    else if (col.DataType == typeof(int))
                    {
                        txt = (TextBox)FindControlR(col.ColumnName);
                        if (txt.Text == "")
                        {
                            param.Value = DBNull.Value;
                        }
                        else
                        {
                            param.Value = Int32.Parse(txt.Text, ci);
                        }
                    }
                    //Text
                    else
                    {
                        txt = (TextBox)FindControlR(col.ColumnName);
                        if (txt.Text == "")
                        {
                            param.Value = DBNull.Value;
                        }
                        else
                        {
                            param.Value = txt.Text;
                        }
                    }
                    cmd.Parameters.Add(param);

                    //For char id Columns
                    //if (col.ColumnName == Request.QueryString["IdColumn"].ToString() && Request.QueryString["IdCt"].ToString() == "string")
                    //{
                    //    param = new SqlParameter();
                    //    param.ParameterName = "@" + col.ColumnName;
                    //    txt = (TextBox)FindControl(col.ColumnName);
                    //    param.Value = txt.Text;
                    //    cmd.Parameters.Add(param);
                    //}
                }
            }

            currentColumnName = "All fields in correct format";

            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Close();

            Response.Redirect("TableContent.aspx?TableName=" + TableName);
        }
        catch (Exception ex)
        {
            lblMessage.Text = string.Format("{0} ({1})", ex.Message, currentColumnName);
        }
    }

    private void SetupForm()
    {
        //Get table definition for fields
        DataTable dt = DBData.SampleRow(Session["ConnectionString"], TableName);

        Table tbl = new Table();
        tbl.BorderWidth = 1;
        TableRow row;

        //Page.Form.Controls.Add(tbl);
        FindControlR("panItemEdit").Controls.Add(tbl);

        foreach (DataColumn col in dt.Columns)
        {
            //Response.Write(col.DataType.ToString() + "<br>");
            AddControl(tbl, col);
        }

        //Buttons
        row = new TableRow();
        TableCell saveButtonCell = new TableCell();
        Button btnSave = new Button();
        btnSave.ID = "btnSave";
        btnSave.Text = "Save";
        btnSave.Click += new EventHandler(btnSave_Click);
        saveButtonCell.Controls.Add(btnSave);
        row.Cells.Add(saveButtonCell);

        TableCell deleteButtonCell = new TableCell();
        Button btnDelete = new Button();
        btnDelete.ID = "btnDelete";
        btnDelete.Text = "Delete";
        btnDelete.OnClientClick = "return confirm('Are you sure you want to delete?');";
        btnDelete.Click += new EventHandler(btnDelete_Click);
        deleteButtonCell.Controls.Add(btnDelete);
        row.Cells.Add(deleteButtonCell);
        tbl.Rows.Add(row);

        //Label message
        //row = new TableRow();
        //labelCell = new TableCell();
        //Label lblMessage = new Label();
        //lblMessage.ID = "lblMessage";
        //lblMessage.Text = "lblMessage";        
        //controlCell = new TableCell();
        //controlCell.Controls.Add(lblMessage);
        //row.Cells.Add(controlCell);
        //tbl.Rows.Add(row);
    }

    void btnDelete_Click(object sender, EventArgs e)
    {
        lblMessage.Text = string.Empty;
        try
        {
            string selectStr = string.Format("SELECT TOP 1 * FROM {0} ", TableName);
            DataTable dt = DBData.SampleRow(Session["ConnectionString"], TableName);

            SqlConnection connection = new SqlConnection(Session["ConnectionString"].ToString());
            SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);

            SqlCommandBuilder builder = new SqlCommandBuilder();
            builder.DataAdapter = adapter;
            builder.ConflictOption = ConflictOption.OverwriteChanges;

            string deleteCommandSql = builder.GetDeleteCommand(true).CommandText;
            SqlCommand cmd = new SqlCommand(deleteCommandSql, connection);

            SqlParameter param = new SqlParameter();
            param.ParameterName = param.ParameterName = "@Original_" + Request.QueryString["IdColumn"].ToString();
            TextBox txt = (TextBox)FindControlR(Request.QueryString["IdColumn"].ToString());
            param.Value = txt.Text;
            cmd.Parameters.Add(param);

            connection.Open();
            cmd.ExecuteNonQuery();
            connection.Close();

            Response.Redirect("TableContent.aspx?TableName=" + TableName);
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
    }

    public Control FindControlR(string controlName)
    {
        return FindControlRecursive(Page, controlName);
    }

    public static Control FindControlRecursive(Control Root, string Id)
    {
        if (Root.ID == Id)
            return Root;

        foreach (Control Ctl in Root.Controls)
        {
            Control FoundCtl = FindControlRecursive(Ctl, Id);
            if (FoundCtl != null)
                return FoundCtl;
        }
        return null;
    }

    private void SetValuesFromDatabase()
    {
        //string selectStr = string.Format("SELECT TOP 1 * FROM {0} WHERE {1}={2}", TableName, Request.QueryString["IdColumn"], Request.QueryString["Id"]);
        //if (Request.QueryString["IdCt"].ToString() == "string")
        //{
        //    selectStr = string.Format("SELECT TOP 1 * FROM {0} WHERE {1}='{2}'", TableName, Request.QueryString["IdColumn"], Request.QueryString["Id"]);
        //}
        //databaseRow = DataAccess.GetDataRow(Session["ConnectionString"].ToString(), selectStr);
        databaseRow = DBData.ItemRow(Session["ConnectionString"], TableName, IdColumn, IdValue);

        foreach (DataColumn col in databaseRow.Table.Columns)
        {
            if (!databaseRow.IsNull(col.ColumnName))
            {
                //if (!databaseRow.IsNull(col.ColumnName))
                //{
                if (col.DataType == typeof(bool))
                {
                    DropDownList ddl = (DropDownList)FindControlR(col.ColumnName);
                    ddl.SelectedValue = databaseRow[col.ColumnName].ToString();
                }
                else if (col.DataType == typeof(DateTime))
                {
                    TextBox txt = (TextBox)FindControlR(col.ColumnName);
                    //txt.Text = Convert.ToDateTime(databaseRow[col.ColumnName]).ToString("dd.MM.yyyy");
                    txt.Text = Convert.ToDateTime(databaseRow[col.ColumnName]).ToString(ci);
                }
                else if (col.DataType == typeof(decimal))
                {
                    TextBox txt = (TextBox)FindControlR(col.ColumnName);
                    txt.Text = Convert.ToDecimal(databaseRow[col.ColumnName]).ToString(ci);
                }
                else
                {
                    TextBox txt = (TextBox)FindControlR(col.ColumnName);
                    txt.Text = databaseRow[col.ColumnName].ToString();
                    if (MappingRow(TableName, col.ColumnName) != null)
                    {
                        DropDownList ddl = (DropDownList)FindControlR("ddl" + col.ColumnName);
                        ddl.SelectedValue = databaseRow[col.ColumnName].ToString();
                    }
                }
            }
            //}
        }
    }

    protected void Page_PreRender(object sender, EventArgs e)
    {
        string scr;

        //scr =
        //    "<script>" + Environment.NewLine +
        //    "function SetTxtFromDdl(t, txtId, ddlId)" + Environment.NewLine +
        //    "{" + Environment.NewLine +
        //    "//alert(t);" + Environment.NewLine +
        //    "//document.getElementById(txtId).value = document.getElementById(ddlId).value; " + Environment.NewLine +
        //    "//document.getElementById('ctl00_ContentPlaceHolder1_ddlGrupaId')" + Environment.NewLine +
        //    "document.getElementById(t.parentElement.children[0].id).value = t.value" + Environment.NewLine +
        //    "}" + Environment.NewLine +
        //    "</script>"
        //    ;

        scr =
            "<script>" + Environment.NewLine +
            "function SetTxtFromDdl(t)" + Environment.NewLine +
            "{" + Environment.NewLine +
            "//alert(t);" + Environment.NewLine +
            "document.getElementById(t.parentElement.children[0].id).value = t.value" + Environment.NewLine +
            "}" + Environment.NewLine +
            "</script>"
            ;
        RegisterClientScriptBlock("key2", scr);
    }

    //private void FillDll(DropDownList ddl, string tableName, string columnName)
    private void FillDll(DropDownList ddl, DataRow mappingRow)
    {
        //SettingsWDB swdb = new SettingsWDB();
        //string path = Server.MapPath("~") + "SettingsWDB.xml";
        //swdb.ReadXml(path);

        string selectStr;
        //selectStr = string.Format("TableName = '{0}' AND ColumnName = '{1}'", tableName, columnName);
        //DataRow mappingRow = DataAccess.GetDataRow(swdb.ReferenceTable, selectStr);

        if (mappingRow != null)
        {
            //selectStr = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {0} DESC ", mappingRow["ReferenceTableColumn"], mappingRow["ReferenceTableDisplayColumn"], mappingRow["ReferenceTable"]);
            selectStr = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {3} {4} ", mappingRow["ReferenceIdColumn"], mappingRow["ReferenceDisplayColumn"], mappingRow["ReferenceObject"], mappingRow["ReferenceOrderByColumn"], mappingRow["ReferenceOrderDirection"]);
            ddl.DataTextField = mappingRow["ReferenceDisplayColumn"].ToString();
            ddl.DataValueField = mappingRow["ReferenceIdColumn"].ToString();
            ddl.DataSource = DataAccess.GetDataTable(Session["ConnectionString"].ToString(), selectStr);
            ddl.DataBind();
            ListItem nullLi = new ListItem(string.Empty, string.Empty);
            ddl.Items.Insert(0, nullLi);
        }
    }
    public DataRow MappingRow(string tableName, string columnName)
    {
        //string selectStr;
        //selectStr = string.Format("TableName = '{0}' AND ColumnName = '{1}'", tableName, columnName);
        //DataRow row = null; // DataAccess.GetDataRow(SWDB.ReferenceTable, selectStr);
        //return row;

        string selectStr = string.Format("SELECT * FROM ColumnMapping WHERE ConnectionId={0} AND TableName = '{1}' AND TableColumnName = '{2}'", ConnectionId, tableName, columnName);
        DataRow row = DataAccess.GetDataRow(DataAccess.ConfigurationConnectionString, selectStr);
        return row;
    }

    protected void btnSave_Click1(object sender, ImageClickEventArgs e)
    {
        try
        {
            Save();
        }
        catch (Exception ex)
        {
            //lblMessage.Text = ex.Message;
        }
    }
}